import pymysql
from todo import Todo
class TodoStorageWithMySQL:
    todo = []
    @staticmethod
    def get_conn():
        return pymysql.Connection(host='192.168.174.131',database='test',user='root',password='root@123ABC',autocommit=True)

    def init_db(self):
        with self.get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute('''
            create table if not exists todos(
            id int primary key not null auto_increment,
            title varchar(50) not null,
            completed int default 0
            );
            ''')

    def create(self,title:str):
        with self.get_conn() as conn:
            cursor = conn.cursor()  #建立连接，得到一个对象
            cursor.execute('insert into todos(title) values(%s)',(title,))  #返回受影响的行数
            cursor.execute('select last_insert_id()')
            (id,)=cursor.fetchone()
            return Todo(title,id = id)
        # with self.get_conn() as conn:
        #     cursor = conn.cursor()  #建立连接，得到一个对象
        #     res = cursor.execute('select * from students')  #返回受影响的行数
        #     data = cursor.fetchone()  #输出表的第一行
        #     data1 = cursor.fetchall()  #输出表的内容
        #     print(data1)
        #     print(data)
        #     print(cursor)
        #     print(res)
    def convert(self ,row):
        return Todo(row[1],id = row[0],completed=True if row[2]==1 else False)


    def list_todos(self):
        with self.get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos')
            data = cursor.fetchall()
            todos = []
            for row in data:
                todos.append(self.convert(row))
        return todos

    def get_todo_byid(self, id):
        with self.get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where id = (%s)',(id,))
            todo = cursor.fetchone()
            if todo is not None:
                return self.convert(todo)
            else:
                return None
    def mark_completed(self,id):
        with self.get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute('update todos set completed = 1 where id = (%s)',(id,))
        return self.get_todo_byid(id)
    def del_todos(self,title):
        with self.get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where title=%s', (title,))
            sar = cursor.fetchone()
            if sar is not None:
                cursor.execute('delete from todos where title=%s', (title,))
                return sar

